import os
from sqlalchemy import Boolean, Column, Float, Integer, String, Text, DateTime, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from datetime import datetime
from sqlalchemy.dialects.postgresql import JSON
from src.config import STATIC_ROOT_DIR

Base = declarative_base()

class Video(Base):
    """Model representing the videos table in the database."""
    __tablename__ = 'videos'
    
    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey('monitored_users.id'))
    platform = Column(String, nullable=False)
    video_id = Column(String, nullable=False)
    title = Column(Text)
    download_url = Column(Text)
    _local_path = Column("local_path", String)
    download_status = Column(String)
    last_download_url = Column(String)
    created_at = Column(DateTime, default=datetime.utcnow)
    download_at = Column(DateTime)
    retry_count = Column(Integer, default=0)
    next_retry_at = Column(DateTime)
    duration = Column(Integer) 
    
    # Relationship with MonitoredUser (assuming it exists)
    user = relationship("MonitoredUser", back_populates="videos")
    
    @property
    def local_path(self) -> str:
        """将数据库中存的相对路径转成绝对路径用于文件操作"""
        if not self._local_path:
            return ""
        return os.path.join(STATIC_ROOT_DIR, self._local_path.lstrip("/\\"))

    @local_path.setter
    def local_path(self, value: str):
        """
        接收绝对路径，转为数据库相对路径存储
        或者你也可以直接接收 web 相对路径
        """
        if value.startswith(STATIC_ROOT_DIR):
            rel_path = value[len(STATIC_ROOT_DIR):].lstrip("/\\")
            self._local_path = rel_path.replace(os.sep, "/")
        else:
            self._local_path = value  # 已是相对路径或 web 路径

    def __repr__(self):
        return f"<Video(id={self.id}, platform='{self.platform}', video_id='{self.video_id}')>"

class MonitoredUser(Base):
    """Model representing the monitored_users table in the database."""
    __tablename__ = 'monitored_users'
    
    id = Column(Integer, primary_key=True, index=True)
    platform = Column(String, nullable=False)
    username = Column(String, nullable=False)
    sec_uid = Column(String)
    user_id = Column(String)
    category = Column(String)
    profile_info = Column(Text)
    last_video_count = Column(Integer)
    created_at = Column(DateTime)
    last_checked = Column(DateTime)
    
    # Relationship with Video
    videos = relationship("Video", back_populates="user")
    
    def __repr__(self):
        return f"<MonitoredUser(id={self.id}, platform='{self.platform}', username='{self.username}')>"

class VideoVectorMapping(Base):
    """Model mapping videos to their vector embeddings in FAISS."""
    __tablename__ = 'video_vector_mappings'
    
    id = Column(Integer, primary_key=True, index=True)
    video_id = Column(Integer, ForeignKey('videos.id'), nullable=False)
    faiss_id = Column(Integer, nullable=False)  # ID in the FAISS index
    vector_type = Column(String)  # Type of embedding (e.g., 'frame', 'audio', 'text')
    frame_number = Column(Integer, nullable=True)  # For frame-specific vectors
    vector_dimension = Column(Integer)  # Dimensionality of the vector
    created_at = Column(DateTime, default=datetime.utcnow)
    
    # Relationship with Video
    video = relationship("Video", backref="vector_mappings")
    
    def __repr__(self):
        return f"<VideoVectorMapping(id={self.id}, video_id={self.video_id}, faiss_id={self.faiss_id})>"

########Abandoned AudioUpload Model########
# This model is kept for reference but is not used in the current implementation.
class AudioUpload(Base):
    """Model representing uploaded files in the database."""
    __tablename__ = 'audio_uploads'
    
    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    audio_path = Column(String, nullable=False)
    subtitle_path = Column(String, nullable=True)  # Optional subtitle file path
    filename = Column(String, nullable=False)
    group_id = Column(Text)
    order = Column(Integer)
    meta_data = Column(JSON)
    created_at = Column(DateTime, default=datetime.utcnow)
    status = Column(String)  # For tracking upload status
    
    def __repr__(self):
        return f"<AudioUpload(id={self.id}, filename='{self.filename}')>"
    
class VideoGenerationConfig(Base):
    """Model for storing video generation configuration settings."""
    __tablename__ = 'video_generation_configs'
    
    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    name = Column(String)  # Optional configuration name/description
    
    # Source configuration
    prompt_data = Column(JSON)  # The prompt data used for generation
    date_range = Column(JSON)  # Date range for video search
    
    # Generation settings
    transition_effect = Column(String)  # Effect type used (fade, wipe, etc.)
    transition_duration = Column(Float)  # Duration of transitions in seconds
    remove_voice = Column(Boolean, default=False)  # Whether voice was removed
    random_limit = Column(Integer)  # Number of videos to randomly select
    generate_all = Column(Boolean, default=False)  # Whether to generate all combinations
    
    # Processing metadata
    created_at = Column(DateTime, default=datetime.utcnow)
    created_by = Column(String)  # User who created this configuration
    
    # Relationships
    generated_videos = relationship("GeneratedVideo", back_populates="config")
    
    def __repr__(self):
        return f"<VideoGenerationConfig(id={self.id}, name='{self.name}')>"

class GeneratedVideo(Base):
    """Model for storing generated video information."""
    __tablename__ = 'generated_videos'
    
    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    config_id = Column(Integer, ForeignKey('video_generation_configs.id'))
    
    # File information
    file_path = Column(String, nullable=False)  # Full path to the video file
    filename = Column(String, nullable=False)   # Just the filename
    output_folder = Column(String)              # Output directory
    
    # Source information
    source_videos = Column(JSON)                # IDs or paths of source videos used
    audio_path = Column(String)                 # Path to audio used (if any)
    
    # Generation metadata
    combination_number = Column(Integer)        # For multi-combination generations
    file_size = Column(Integer)                 # Size in bytes
    duration = Column(Float)                    # Duration in seconds
    
    # Processing status
    status = Column(String)                     # 'completed', 'failed', 'processing'
    error_message = Column(Text)                # Error information if failed
    
    created_at = Column(DateTime, default=datetime.utcnow)
    
    # Relationships
    config = relationship("VideoGenerationConfig", back_populates="generated_videos")
    
    def __repr__(self):
        return f"<GeneratedVideo(id={self.id}, filename='{self.filename}', status='{self.status}')>"

class UploadedFile(Base):
    __tablename__ = 'uploaded_files'

    id = Column(Integer, primary_key=True, index=True)
    filename = Column(String, nullable=False)          # 原始文件名
    file_path = Column(String, nullable=False)         # 存储路径（相对或绝对）
    file_type = Column(String, nullable=False)         # intro / outro / audio / subtitle / bgm / logo / other
    uploaded_by = Column(String)                       # 上传用户
    created_at = Column(DateTime, default=datetime.utcnow)
    meta_data = Column(JSON, nullable=True)            # 可选，比如时长、大小等
    status = Column(String, default='active')          # 可扩展状态，如 active/deleted

    def __repr__(self):
        return f"<UploadedFile(id={self.id}, file_type='{self.file_type}', filename='{self.filename}')>"
